<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Public_model extends CI_Model 
{
	function __construct()
	{
		parent:: __construct();
	}

	/*********************************************************
	**	MODULE MEETING ROOM									**
	**	AUTHOR 			: ERY								**
	**	DATE CREATED	:									**
	**	MODIFIED DATE	: 22/05/2014						**
	**	MODIFIED BY		: Ery								**
	*********************************************************/

	function list_data_item() {     //tambahan by Surya Alam Gultom - Jumat, 4 Juli 2014 10:59 AM
	
		$sql_surya = " 
			select warehouse_id, warehouse_item_name, warehouse_item_qty, warehouse_item_qty_in, warehouse_item_qty_out  
			from fm_warehouse where warehouse_item_status = 'Y' and warehouse_item_qty_in > 0 order by warehouse_item_name 
		";
		
		$query = $this->db->query($sql_surya);
        return $query->result_array();
	}
	
	function count_data() {     //tambahan by Surya Alam Gultom - Jumat, 4 Juli 2014 10:59 AM
	
		$sql_surya = " 
			select COUNT (*) cnt from fm_warehouse where warehouse_item_status = 'Y' and warehouse_item_qty_in > 0 
		";
		
		$result = $this->db->query($sql_surya)->row();
        return $result->CNT;
	}	
	
	/*================================================================
		Function to Get Booking Name
		Last Edit : 22/05/2014
		By : Ery
	=================================================================*/
	/*
	function getRequestor($roomId)
	{
		$sql = "SELECT count(*) CNT FROM FM_MEETING_ROOM WHERE room_id = '".$roomId."'";
		$result = $this->db->query($sql);
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	*/
	/*================================================================
		Function to Get Meeting Room Floor
		Last Edit : 22/05/2014
		By : Ery
	=================================================================*/
	function getFloor()
	{
		$sql = "SELECT
				mst_flo.MST_FLOOR_DESC
				FROM fm_mst_category mst_cat
				INNER JOIN fm_mst_floor mst_flo ON mst_flo.MST_FLOOR_ID = mst_cat.MST_FLOOR_ID AND mst_flo.MST_TYPE_ID = mst_cat.MST_TYPE_ID
				WHERE mst_cat.MST_CATEGORY_STATUS = 'Y' ORDER BY mst_flo.MST_FLOOR_DESC ASC";
		$result = $this->db->query($sql);
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	
	
	function getRoomFloor($floor_id)	//tambahan by Surya Alam Gultom - Jumat, 25 Juli 2014 11:39 AM
	{
		$sql = "
			SELECT mst_floor_desc FROM fm_mst_floor WHERE mst_floor_id = ".$floor_id." 			
		";
				
		$result = $this->db->query($sql);
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
		
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function getRoomCapacity($cat_id)	//tambahan by Surya Alam Gultom - Jumat, 25 Juli 2014 11:46 AM
	{
		$sql = "
			SELECT mst_category_capacity FROM fm_mst_category WHERE mst_category_id = ".$cat_id." 			
		";
				
		$result = $this->db->query($sql);
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
		
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function getRoomType($cat_id)	//tambahan by Surya Alam Gultom - Rabu, 13 Agustus 2014 3:30 PM
	{
		$sql = "
			SELECT typ.mst_type_desc type  
			FROM fm_mst_category cat 
			INNER JOIN fm_mst_type typ ON cat.mst_type_id = typ.mst_type_id 
			WHERE cat.mst_category_id = ".$cat_id." 			
		";
				
		$result = $this->db->query($sql);
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
		
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}

	/*================================================================
		Function to Get Room List
		Last Edit : 22/05/2014
		By : Ery
	=================================================================*/
	function getRoomList()
	{
		$sql = "SELECT
				mst_cat.MST_CATEGORY_ID,
				mst_flo.MST_FLOOR_DESC,
				mst_cat.MST_CATEGORY_DESC,
				nvl(mst_cat.MST_CATEGORY_CAPACITY,0) MST_CATEGORY_CAPACITY,
				mst_flo.MST_FLOOR_ID
				FROM fm_mst_category mst_cat
				INNER JOIN fm_mst_floor mst_flo ON mst_flo.MST_FLOOR_ID = mst_cat.MST_FLOOR_ID AND mst_flo.MST_TYPE_ID = mst_cat.MST_TYPE_ID
				WHERE mst_cat.MST_CATEGORY_STATUS = 'Y' ORDER BY mst_flo.MST_FLOOR_DESC ASC";
				
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function getMeetingRoomList()
	{
		$sql = "SELECT
				mst_cat.MST_CATEGORY_ID,
				mst_flo.MST_FLOOR_DESC,
				mst_cat.MST_CATEGORY_DESC,
				nvl(mst_cat.MST_CATEGORY_CAPACITY,0) MST_CATEGORY_CAPACITY,
				mst_flo.MST_FLOOR_ID
				FROM fm_mst_category mst_cat
				INNER JOIN fm_mst_floor mst_flo ON mst_flo.MST_FLOOR_ID = mst_cat.MST_FLOOR_ID AND mst_flo.MST_TYPE_ID = mst_cat.MST_TYPE_ID
				WHERE mst_cat.MST_CATEGORY_STATUS = 'Y' AND upper(mst_cat.MST_CATEGORY_DESC) like '%MEETING%'   
				ORDER BY mst_flo.MST_FLOOR_DESC ASC";
				
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}

	/*================================================================
		Function to Get Room properties
		Last Edit : 22/05/2014
		By : Ery
	=================================================================*/
	function getRoomProp($cat_id = 0)
	{
		if($cat_id == 0)
		{
			return FALSE;
		}
		else
		{
			$sql = "SELECT(SELECT upper(room.ITEM_PUBLIC_ROOM_NAME)  
					FROM FM_MST_ITEM_PUBLIC_ROOM room 
					WHERE room.ITEM_PUBLIC_ROOM_ID = categ.ITEM_PUBLIC_ROOM_ID) ITEM
					FROM FM_MST_ITEM_CATEGORY categ
					WHERE categ.MST_TYPE_ID = 1 AND categ.MST_CATEGORY_ID = ".$cat_id;
			$result = $this->db->query($sql);
			$Udata = $result->result_array();
			$Rdata = $result->num_rows();
			if ($Rdata > 0)
			{
				return $Udata;
			}
			else
			{
				return FALSE;
			}
		}
	}
	
	/*================================================================
		Function to Get Time List
		Last Edit : 22/05/2014
		By : Ery
	=================================================================*/
	function getFullTime()
	{
		$sql = "SELECT TRIM(TIME_ID) TIME_ID, TRIM(TIME_NAME) TIME_NAME, 'Y' AS TIME_STATUS FROM FM_MST_TIME order by TIME_NAME ";
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}

	/*================================================================
		Function to Get Meeting Room Available
		Last Edit : 22/05/2014
		By : Ery
	=================================================================*/
	function getRoomAvailable($room_name,$floor_id,$dateSelect)
	{
		if($room_name == "Select Room")
		{
			return FALSE;
		}
		else
		{
			$sqlv = "SELECT time_id FROM FM_MST_TIME
					WHERE 
					time_name IN (
						SELECT p.timestart
						FROM (
								SELECT trim(MROOM.ROOM_SHOUR||':'||MROOM.ROOM_SMINUTE) timestart FROM FM_MEETING_ROOM MROOM 
								WHERE trunc(MROOM.ROOM_DATE) = to_date('".$dateSelect."','dd/mm/yyyy')
								AND 
								MROOM.room_floor = (SELECT distinct floor.MST_FLOOR_DESC FROM FM_MST_FLOOR floor WHERE floor.MST_FLOOR_ID = ".$floor_id.")
								AND MROOM.room_type = '".$room_name."'
								UNION ALL
								SELECT TRIM(MROOM.ROOM_EHOUR||':'||MROOM.ROOM_EMINUTE) timestart
								FROM FM_MEETING_ROOM MROOM
								WHERE trunc(MROOM.ROOM_DATE) = to_date('".$dateSelect."','dd/mm/yyyy')
								AND 
								MROOM.room_floor = (SELECT distinct floor.MST_FLOOR_DESC FROM FM_MST_FLOOR floor WHERE floor.MST_FLOOR_ID = ".$floor_id.")
								AND MROOM.room_type = '".$room_name."'
							)p
					) order by time_name ";
					
			//echo($sqlv); return false;		
			
			$result = $this->db->query($sqlv);
			$Udata = $result->result_array();
			$Rdata = $result->num_rows();
			if ($Rdata > 0)
			{
				return $Udata;
			}
			else
			{
				return FALSE;
			}
		}
	}
	
	/*================================================================
		Function to Get Meeting Room Name Available
		Last Edit : 22/05/2014
		By : Ery
	=================================================================*/

	function getRoomName($room_name,$floor_id,$dateSelect,$aTimeId)
	{
		$sql =" SELECT MROOM.room_description, MROOM.room_id, (SELECT hr.name FROM HRS hr WHERE hr.id = MROOM.usrupd) as name
				FROM FM_MEETING_ROOM MROOM
				WHERE TRUNC(MROOM.room_date) = to_date('".$dateSelect."','dd/mm/yyyy')
				AND MROOM.ROOM_SHOUR||':'||MROOM.ROOM_SMINUTE = (SELECT time_name FROM fm_mst_time WHERE time_id = ".$aTimeId.")
				AND MROOM.room_floor = (SELECT distinct floor.MST_FLOOR_DESC FROM FM_MST_FLOOR floor WHERE floor.MST_FLOOR_ID = ".$floor_id.")
				AND MROOM.room_type = '".$room_name."'";
				
		$result = $this->db->query($sql);
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}


	/*********************************************************
	**	MODULE MAILING ROOM									**
	**	AUTHOR 			: ERY								**
	**	DATE CREATED	:									**
	**	MODIFIED DATE	: 22/05/2014						**
	**	MODIFIED BY		: Ery								**
	*********************************************************/
	
	function getTimePick()
	{
		$sql = "select case when 
			(to_date(to_char(sysdate,'dd/mm/yyyy hh24:mi'),'dd/mm/yyyy hh24:mi') <
			to_date(to_char(sysdate,'dd/mm/yyyy')||' '||'10:00','dd/mm/yyyy hh24:mi')) and 
			(to_date(to_char(sysdate,'dd/mm/yyyy hh24:mi'),'dd/mm/yyyy hh24:mi') <
			to_date(to_char(sysdate,'dd/mm/yyyy')||' '||'15:00','dd/mm/yyyy hh24:mi')) then 1 
		when 
			(to_date(to_char(sysdate,'dd/mm/yyyy hh24:mi'),'dd/mm/yyyy hh24:mi') >
			to_date(to_char(sysdate,'dd/mm/yyyy')||' '||'10:00','dd/mm/yyyy hh24:mi')) and 
			(to_date(to_char(sysdate,'dd/mm/yyyy hh24:mi'),'dd/mm/yyyy hh24:mi') <
			to_date(to_char(sysdate,'dd/mm/yyyy')||' '||'15:00','dd/mm/yyyy hh24:mi')) then 2
		
		else 3 end as test  
		from dual";
		
		$result = $this->db->query($sql);
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function getNameReq($R_Name)
	{
		$sql = "SELECT b.ID, b.NAME FROM LOGINS a, HRS b WHERE a.hr_id = b.id AND LOWER(b.NAME) LIKE LOWER('%".$R_Name."%')";
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function SumNameReq($R_Name)
	{
		$sql = "SELECT COUNT(b.NAME) AS Counting 
				FROM LOGINS a, HRS b 
				WHERE a.hr_id = b.id AND LOWER(b.NAME) LIKE LOWER('%".$R_Name."%')";
		$result = $this->db->query($sql);
		$Udata = $result->row_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function getDetailEmp($R_Nik)
	{
		$sql	= "	SELECT B.HR_ID, C.NAME, C.ORG, C.JOB_POSITION, (SELECT DISTINCT(NAME) NAME FROM HRS WHERE ID = C.MANAGER) MANAGER  
					FROM LOGINS B, HRS C 
					WHERE B.HR_ID = C.ID AND C.ID = ".$R_Nik;
		$result = $this->db->query($sql);
		$Udata 	= $result->row_array();
		$Rdata 	= $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function getArea($cond)
	{
		$sql 	= " SELECT DISTINCT(AREA_NAME)
					FROM FM_MST_AREA
					WHERE AREA_STATUS = 'Y' ".$cond."
					ORDER BY AREA_NAME ASC";
				
		$result = $this->db->query($sql);
		$Udata 	= $result->result_Array();
		$Rdata 	= $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function getRegional($Reg_name)
	{
		if(strlen($Reg_name) > 0)
		{
			 $cond = "AND LOWER(AREA_NAME) = LOWER('".$Reg_name."')";
		}
		else
		{
			 $cond = "";
		}
		$sql 	= " SELECT AREA_ID, REGIONAL_NAME
					FROM FM_MST_AREA
					WHERE AREA_STATUS = 'Y' ".$cond."
					ORDER BY REGIONAL_NAME ASC";
		$result = $this->db->query($sql);
		$Udata 	= $result->result_Array();
		$Rdata 	= $result->num_rows();		
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	function getfilRegional($Reg_Name)
	{

		$sql 	= " SELECT AREA_ID, REGIONAL_NAME
					FROM FM_MST_AREA
					WHERE AREA_STATUS = 'Y' AND LOWER(REGIONAL_NAME) LIKE LOWER('%".$Reg_Name."%')
					ORDER BY REGIONAL_NAME ASC";
		$result = $this->db->query($sql);
		$Udata 	= $result->result_Array();
		$Rdata 	= $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
	
	//===============================================================================================================
    // INSERT MAILING ROOM CONTROLLER ~ SURYA ALAM GULTOM 
    //===============================================================================================================
	
	function insert_dmr($arr) 
	{
		$r_id 		= $arr['r_id'];
		$r_mgr		= $arr['r_mgr'];
		$courier 	= $arr['courier'];
        $category 	= $arr['category']; 
        $cargo_type = $arr['cargo_type'];
        $location 	= $arr['location'];
        $timed 		= $arr['timed'];
        $pDmTujuan 	= $arr['D_Area'];
        $D_Regional = $arr['D_Regional'];
        $lantai 	= $arr['lantai'];
        $pDmBerat	= $arr['weight'];
		$deskripsi 	= $arr['deskripsi'];
				
		if($location == "Dmr") {
		   $aFloor = 0;
		}else {
		   $aFloor = $lantai ;
		}
		
		// GET REGIONAL NAME
		if(empty($D_Regional)) {			
			$sql = "
				select REGIONAL_NAME
				from fm_mst_area area 
				where AREA_NAME = '$pDmTujuan'
				and rownum = 1
			";			
			$vRegional = $this->db->query($sql)->row_array();
			$pDmRegional = $vRegional['REGIONAL_NAME'];			
		}
		
		// GET TUJUAN ID
		$getTujuan = "
			select area_id from FM_MST_AREA where area_name = '$pDmTujuan' and regional_name = '$pDmRegional' 
		";
		$vArea = $this->db->query($getTujuan)->row_array();
		$vAreaId = $vArea['AREA_ID'];			
		
		// GET PRICE 
		$getprice = "
			select 
				price.PRICE_1KG,
				price.PRICE_5KG,
				price.PRICE_15KG
			from FM_COURIER_PRICE price
			where price.courier_id = $courier 
			and price.area_id = $pDmTujuan 
		";
		
		$row = $this->db->query($getprice)->row_array();
		$aKg = $row['PRICE_1KG'];
		$bKg = $row['PRICE_5KG'];
		$cKg = $row['PRICE_15KG'];
		
		if($pDmBerat <= 1) {
			$biaya = $aKg ;
		} else if($pDmBerat > 1 && $pDmBerat <= 5) {
			$biaya = $bKg ;
		} else {
			$biaya = $cKg ;
		}
		
		// GET COURIER
		$getKurir = " 
			select courier_name
	        from fm_mst_courier 
			where courier_id = $courier
		" ;
		
		$a = $this->db->query($getKurir)->row_array();
		$vKurirName = $a['COURIER_NAME'];
		
		// GET MAX DMR ID		
		$qry1 = "
			select max(to_number(substr(dmr_id,1,5),'99999')) dmr_id_old, rtrim(to_char(sysdate,'RM')) romawi from fm_dmr 
		";
		
		$b = $this->db->query($qry1)->row_array();
		$cDmrIdOld = $b["DMR_ID_OLD"];	
		$cRomawi   = $b["ROMAWI"];	
		$cDmrIdNew = $cDmrIdOld +1;
		
		$qry2 = "
			select lpad($cDmrIdNew,5,0)||'/DMR/$cRomawi/'||TO_CHAR(SYSDATE,'YYYY') DMR_ID from dual 
		";
		
		$a = $this->db->query($getKurir)->row_array();
		$cNoTrafficID = $a["DMR_ID"];	
		
		$ins1 = "
			insert into fm_dmr dmr (
				dmr.dmr_id, 
				hr_id, 
				dmr.dmr_tujuan, 
				dmr.dmr_penjemputan,
	            dmr.dmr_recieve_date, 
				dmr.courier_id,
				dmr.dmr_description, 
				dmr.dmr_type, 
				DMR_ATASAN_APP_ID, 
				DMR_FLOOR, 
				DMR_CATEGORY, 
				DMR_WAKTU_PENJEMPUTAN, 
				DMR_COURIER_TYPE, 
				DMR_UNTUK_NIK, 
				DMR_REGIONAL, 
				DMR_REGIONAL2 ) 
			values (
				'$cNoTrafficID', 
				'$nik', 
				'$pDmTujuan', 
				'$location',
				trunc(sysdate), 				
				'$courier_id', 				
				'$deskripsi', 
				'$cargo_type', 				
				'$pAtasanNik', 				
				'$lantai', 
				'$category', 
				'$timed', 
				'$pJasaKurir', 
				'$r_id',
				'$D_Regional', 
				''
			) 
		";
		$this->db->query($ins1);
			
		// Start Sms
		$msg = "FM : Order No $cNoTrafficID telah berhasil, untuk Proses selanjutnya menunggu Approval dari atasan anda $vAtasanName.";
		$msgatasan = "FM : Order No $cNoTrafficID membutuhkan approval anda.";
				
		//Sms to User
		//$msisdn = dbora_getgamsisdn($pNikFor);
		//send_sms($msisdn, $msg);
		//End Sms User
		
		//Sms to Atasan
		//$msisdnatasan = dbora_getgamsisdn($pAtasanNik);
		//send_sms($msisdnatasan, $msgatasan);
		//End Sms Atasan
		
		return $msg;
		
	}
	
	function getDetailReq($arr)
	{
		$Nik 		= $arr['Nik'];
		
		$sql	= "	SELECT B.HR_ID, C.NAME, C.ORG, C.JOB_POSITION, (SELECT DISTINCT(NAME) NAME FROM HRS WHERE ID = C.MANAGER) MANAGER, C.EMPLOYEE_TYPE
					FROM LOGINS B, HRS C 
					WHERE NOT C.NAME = 'Administrator' AND B.HR_ID = C.ID AND C.ID = ".$Nik;
					
		$result = $this->db->query($sql);
		$Udata 	= $result->row_array();
		$Rdata 	= $result->num_rows();
		
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}

	function getRequest()
	{
		$sql = "SELECT b.ID, b.NAME FROM LOGINS a, HRS b WHERE NOT b.NAME = 'Administrator' AND a.hr_id = b.id ORDER BY b.NAME ";
		$result = $this->db->query($sql);
		$Udata = $result->result_array();
		$Rdata = $result->num_rows();
		if ($Rdata > 0)
		{
			return $Udata;
		}
		else
		{
			return FALSE;
		}
	}
}
